package com.edu.hre.db.sql.pim;

import java.io.Serializable;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import com.edu.hre.common.utils.CommonUtils;
import com.edu.hre.common.utils.StringTool;
import com.edu.hre.db.pojo.HreEmployee;
import com.edu.hre.db.pojo.HreUser;
import com.edu.hre.db.service.BaseService;
import com.edu.hre.db.utils.Compare;
import com.edu.hre.struts.form.pim.EmployeeForm;
import com.edu.hre.struts.form.pim.EmployeeListForm;

public class EmployeeDAOService implements Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = 3394301416150421253L;
	public static final String BEAN_NAME = "employeeDAOService";
	private BaseService baseService  = null;
	 
	public void setBaseService(BaseService baseService) {
		this.baseService = baseService;
	} 

	public List<EmployeeForm> getListEmployeeInfo(EmployeeListForm form, int limit, int offset){
		
		List<EmployeeForm> listResult = new ArrayList<EmployeeForm>();
		String sql = "";
		ArrayList<Object> params = new ArrayList<Object>();
		
		boolean isSupervisorCond = form.getSupervisorName() != null && !"".equals(form.getSupervisorName());
		boolean isIdCond = form.getId() != null && !"".equals(form.getId().trim());
		boolean isEmpNameCond = form.getEmployeeName() != null && !"".equals(form.getEmployeeName().trim());
		boolean isJobTitleCond = form.getValueListJobTitle() != null && form.getValueListJobTitle() > 0;
		boolean isEmpStatCond = form.getValueListEmploymentStatus() != null && form.getValueListEmploymentStatus() != 0;
		boolean isSubUnitCond = form.getValueListSubunit() != null && form.getValueListSubunit() != 1;
		boolean isIncludeCond = form.getValueListInclude() != null && !"".equals(form.getValueListInclude());

		if (isSupervisorCond) {
			
			sql = "SELECT emp.emp_number AS col1," +
						" emp.emp_firstname AS col2," +
						" CONCAT(emp.emp_lastname, ' ', emp.emp_middle_name)  AS col3, " +
						" job.job_title AS col4, " +
						" sta.name AS col5, " +
						" sub.name AS col6, " +
						" emp.main_username AS col7 " +
						
						" FROM hre_emp_reportto AS erep" +
						" LEFT OUTER JOIN hre_employee AS sup ON erep.erep_sup_emp_number = sup.emp_number" +
						" LEFT OUTER JOIN hre_employee AS emp ON erep.erep_sub_emp_number = emp.emp_number" +
						" LEFT OUTER JOIN hre_job_title AS job ON emp.job_title_code = job.id" +
						" LEFT OUTER JOIN hre_employment_status AS sta ON emp.emp_status = sta.id" +
						" LEFT OUTER JOIN hre_subunit AS sub ON emp.work_station = sub.id" +

						" WHERE 1 = 1 " +
						" AND (CONCAT(sup.emp_lastname, ' ', sup.emp_middle_name, ' ', sup.emp_firstname, ' [', sup.main_username, ']') LIKE '%" + form.getSupervisorName().trim() + "%' " +
						" OR CONCAT(sup.emp_lastname, ' ', sup.emp_firstname, ' [', sup.main_username, ']') LIKE '%" + form.getSupervisorName().trim() + "%' )";

		} else {
		
			sql = "SELECT emp.emp_number AS col1," +
						" emp.emp_firstname AS col2," +
						" CONCAT(emp.emp_lastname, ' ', emp.emp_middle_name)  AS col3, " +
						" job.job_title AS col4, " +
						" sta.name AS col5, " +
						" sub.name AS col6, " +
						" emp.main_username AS col7 " +
						
						" FROM hre_employee AS emp" +
						" LEFT OUTER JOIN hre_job_title AS job ON emp.job_title_code = job.id" +
						" LEFT OUTER JOIN hre_employment_status AS sta ON emp.emp_status = sta.id" +
						" LEFT OUTER JOIN hre_subunit AS sub ON emp.work_station = sub.id" +
						
						" WHERE 1 = 1 ";
		}
		
		if (isIdCond) {
			sql += " AND emp.main_username LIKE '%" + form.getId().trim() + "%'";
		}
		if (isEmpNameCond) {
			String tmpName = form.getEmployeeName().trim();
			while(tmpName.contains("  ")){
				tmpName = tmpName.replace("  ", " ");
			}
			if(tmpName.contains("[") && tmpName.contains("]")){
				sql += " AND " +
						"(" +
							"CONCAT(emp.emp_lastname, ' ', emp.emp_middle_name, ' ', emp.emp_firstname, ' [', emp.main_username, ']') LIKE '%" + tmpName + "%'" + 
							" OR CONCAT(emp.emp_lastname, ' ', emp.emp_firstname, ' [', emp.main_username, ']') LIKE '%" + tmpName + "%'" +
						")";	
			} else {
				sql += " AND " +
						"(" +
							"CONCAT(emp.emp_lastname, ' ', emp.emp_middle_name, ' ', emp.emp_firstname) LIKE '%" + tmpName + "%'" + 
							" OR CONCAT(emp.emp_lastname, ' ', emp.emp_firstname) LIKE '%" + tmpName + "%'" +
							" OR emp.main_username LIKE '%" + tmpName + "%'" +
						")";	
			}
		}
		if (isJobTitleCond) {
			sql += " AND emp.job_title_code = ?";
			params.add(form.getValueListJobTitle());
		}
		if (isEmpStatCond) {
			sql += " AND emp.emp_status = ?";
			params.add(form.getValueListEmploymentStatus());
		}
		if (isSubUnitCond) {
			sql += " AND emp.work_station = ?";
			params.add(form.getValueListSubunit());
		}
		if (isIncludeCond) {
			if ("0".equals(form.getValueListInclude().trim())) {
				sql += " AND emp.is_deleted = 0 ";
			} else if ("2".equals(form.getValueListInclude().trim())) {
				sql += " AND emp.is_deleted = 1 ";
			}
		} else {
			sql += " AND emp.is_deleted = 0 ";
		}

		sql += " ORDER BY emp.date_created DESC";

		List <Object[]> listObject = baseService.listArrayObject(sql, limit, offset, params.toArray());
		for(int i = 0; listObject != null && i < listObject.size(); i++){
			
			try {

				Object[] objs = listObject.get(i);
				EmployeeForm employeeInfo = new EmployeeForm();

				employeeInfo.setEmpNumber(CommonUtils.normalizeObjectToString(objs[1], "0"));
				employeeInfo.setFirstName(CommonUtils.normalizeObjectToString(objs[2], ""));
				employeeInfo.setLastAndMiddleName(CommonUtils.normalizeObjectToString(objs[3], ""));
				employeeInfo.setJobTitle(CommonUtils.normalizeObjectToString(objs[4], ""));
				employeeInfo.setEmploymentStatus(CommonUtils.normalizeObjectToString(objs[5], ""));
				employeeInfo.setSubUnit(CommonUtils.normalizeObjectToString(objs[6], ""));
				employeeInfo.setUsername(CommonUtils.normalizeObjectToString(objs[7], ""));
				employeeInfo.setSupervisor(getAllSupervisorOfEmp(employeeInfo.getEmpNumber()));

				listResult.add(employeeInfo);
			} catch (Exception e) {
				CommonUtils.println(e.getMessage());
			}
		}

		return listResult;
	}
	
	public String getAllSupervisorOfEmp (String id) {

		String sql = "SELECT CONCAT(sup.emp_lastname, ' ', sup.emp_middle_name, ' ', sup.emp_firstname) AS col1 " +

					" FROM hre_emp_reportto AS rep" +
					" LEFT OUTER JOIN hre_employee AS emp ON rep.erep_sub_emp_number = emp.emp_number" +
					" LEFT OUTER JOIN hre_employee AS sup ON rep.erep_sup_emp_number = sup.emp_number" +

					" WHERE sup.is_deleted = 0 " +
					" AND emp.emp_number = ?";
		
		List <Object[]> listObject = baseService.listArrayObject(sql, 0, 0, id);
		
		String result = "";

		for (Object[] objs: listObject) {

			try {
				result += CommonUtils.normalizeObjectToString(objs[1], "") + ", ";	
			} catch (Exception e) {
				CommonUtils.println(e.getMessage());
			}
		}
		
		if (result.length() < 2) {
			return result;
		}

		return result.substring(0, result.length() - 2);
	}
	
	public int countEmployeeInfo(EmployeeListForm form){

		String sql = "";
		ArrayList<Object> params = new ArrayList<Object>();
		
		boolean isSupervisorCond = form.getSupervisorName() != null && !"".equals(form.getSupervisorName().trim());
		boolean isIdCond = form.getId() != null && !"".equals(form.getId().trim());
		boolean isEmpNameCond = form.getEmployeeName() != null && !"".equals(form.getEmployeeName().trim());
		boolean isJobTitleCond = form.getValueListJobTitle() != null && form.getValueListJobTitle() > 0;
		boolean isEmpStatCond = form.getValueListEmploymentStatus() != null && form.getValueListEmploymentStatus() != 0;
		boolean isSubUnitCond = form.getValueListSubunit() != null && form.getValueListSubunit() != 1;
		boolean isIncludeCond = form.getValueListInclude() != null && !"".equals(form.getValueListInclude());
		
		if (isSupervisorCond) {

			sql = "SELECT COUNT(*) AS col1" +
		
					" FROM hre_emp_reportto AS erep" +
					" LEFT OUTER JOIN hre_employee AS sup ON erep.erep_sup_emp_number = sup.emp_number" +
					" LEFT OUTER JOIN hre_employee AS emp ON erep.erep_sub_emp_number = emp.emp_number" +
					" LEFT OUTER JOIN hre_job_title AS job ON emp.job_title_code = job.id" +
					" LEFT OUTER JOIN hre_employment_status AS sta ON emp.emp_status = sta.id" +
					" LEFT OUTER JOIN hre_subunit AS sub ON emp.work_station = sub.id" +

					" WHERE 1 = 1 " +
					" AND (CONCAT(sup.emp_lastname, ' ', sup.emp_middle_name, ' ', sup.emp_firstname, ' [', sup.main_username, ']') LIKE '%" + form.getSupervisorName().trim() + "%' " +
					" OR CONCAT(sup.emp_lastname, ' ', sup.emp_firstname, ' [', sup.main_username, ']') LIKE '%" + form.getSupervisorName().trim() + "%' )";
			
		} else {
		
			sql = "SELECT COUNT(*) AS col1" +
			
					" FROM hre_employee AS emp" +
					" LEFT OUTER JOIN hre_job_title AS job ON emp.job_title_code = job.id" +
					" LEFT OUTER JOIN hre_employment_status AS sta ON emp.emp_status = sta.id" +
					" LEFT OUTER JOIN hre_subunit AS sub ON emp.work_station = sub.id" +
					
					" WHERE 1 = 1 ";
		}
		
		if (isIdCond) {
			sql += " AND emp.main_username LIKE '%" + form.getId().trim() + "%'";
		}
		if (isEmpNameCond) {
			String tmpName = form.getEmployeeName().trim();
			while(tmpName.contains("  ")){
				tmpName = tmpName.replace("  ", " ");
			}
			if(tmpName.contains("[") && tmpName.contains("]")){
				sql += " AND " +
						"(" +
							"CONCAT(emp.emp_lastname, ' ', emp.emp_middle_name, ' ', emp.emp_firstname, ' [', emp.main_username, ']') LIKE '%" + tmpName + "%'" + 
							" OR CONCAT(emp.emp_lastname, ' ', emp.emp_firstname, ' [', emp.main_username, ']') LIKE '%" + tmpName + "%'" +
						")";	
			} else {
				sql += " AND " +
						"(" +
							"CONCAT(emp.emp_lastname, ' ', emp.emp_middle_name, ' ', emp.emp_firstname) LIKE '%" + tmpName + "%'" + 
							" OR CONCAT(emp.emp_lastname, ' ', emp.emp_firstname) LIKE '%" + tmpName + "%'" +
							" OR emp.main_username LIKE '%" + tmpName + "%'" +
						")";	
			}
		}
		if (isJobTitleCond) {
			sql += " AND emp.job_title_code = ?";
			params.add(form.getValueListJobTitle());
		}
		if (isEmpStatCond) {
			sql += " AND emp.emp_status = ?";
			params.add(form.getValueListEmploymentStatus());
		}
		if (isSubUnitCond) {
			sql += " AND emp.work_station = ?";
			params.add(form.getValueListSubunit());
		}
		if (isIncludeCond) {
			if ("0".equals(form.getValueListInclude().trim())) {
				sql += " AND emp.is_deleted = 0 ";
			} else if ("2".equals(form.getValueListInclude().trim())) {
				sql += " AND emp.is_deleted = 1 ";
			}
		} else {
			sql += " AND emp.is_deleted = 0 ";
		}

		try {
			List <Object[]> listObject = baseService.listArrayObject(sql, 0, 0, params.toArray());
			Object o [] = listObject.get(0);
	
			return ((BigInteger) o[1]).intValue();
		} catch (Exception e) {
			CommonUtils.println(e.getMessage());
			return 0;
		}
	}
	
	public void deleteEmployees(EmployeeListForm form){

		ArrayList<EmployeeForm> info = (ArrayList<EmployeeForm>) form.getListEmployeeInfo();
		for (int i = 0; i < info.size(); i++) {
			
			try {

				EmployeeForm emp = info.get(i);
				if (emp.isSelected()) { 
	
					HreEmployee tmp = baseService.getFirstPojo("empNumber", Compare.EQUAL, Integer.parseInt(emp.getEmpNumber()), null, null, 0, HreEmployee.class);
					tmp.setIsDeleted(true);
					baseService.saveOrUpdate(tmp);
					
					// Delete all the related users.
					List<HreUser> relatedUsers = baseService.getPojos("empNumber", Compare.EQUAL, tmp.getEmpNumber(), null, null, -1, 0, HreUser.class);
					if (relatedUsers != null) {
						for (HreUser toDelete: relatedUsers) {
							toDelete.setDeleted(true);
							baseService.saveOrUpdate(toDelete);
						}
					}
				}
			} catch (Exception e) {
				CommonUtils.println(e.getMessage());
			}
		}
	}
	
	public List<EmployeeForm> getAllEmpSuggestions (String keyword) {
		
		String sql = "";
		ArrayList<Object> params = new ArrayList<Object>();
	
		sql = "SELECT emp.emp_number AS col1," +
					" CONCAT(emp.emp_lastname, ' ', emp.emp_middle_name, ' ', emp.emp_firstname)  AS col2, " +
					" emp.main_username AS col3 " +
					
					" FROM hre_employee AS emp" +
					
					" WHERE emp.is_deleted = 0 " +
					" AND (CONCAT(emp.emp_lastname, ' ', emp.emp_middle_name, ' ', emp.emp_firstname, ' [', emp.main_username, ']') LIKE '%" + keyword + "%'" +
					" OR CONCAT(emp.emp_lastname, ' ', emp.emp_firstname, ' [', emp.main_username, ']') LIKE '%" + keyword + "%')";
			
		List <Object[]> listObject = baseService.listArrayObject(sql, -1, 0, params.toArray());
		
		List<EmployeeForm> listResult = new ArrayList<EmployeeForm>();
		for(int i = 0; listObject != null && i < listObject.size(); i++){
			
			try {

				Object[] objs = listObject.get(i);
				EmployeeForm employeeInfo = new EmployeeForm();

				employeeInfo.setEmpNumber(CommonUtils.normalizeObjectToString(objs[1], "0"));
				employeeInfo.setFirstName(CommonUtils.normalizeObjectToString(objs[2], "") +
						" [" + CommonUtils.normalizeObjectToString(objs[3], "") + "]");
				listResult.add(employeeInfo);
			} catch (Exception e) {
				CommonUtils.println(e.getMessage());
			}
		}

		return listResult;
	}
	
	public String getNewUsername (String firstName, String middleName, String lastName) {

		if (firstName == null || middleName == null || lastName == null || firstName.length() <= 0
				|| lastName.length() <= 0) {
			return null;
		}
		
		String _firstName = StringTool.unicode2KoDau(firstName);
		String _middleName = StringTool.unicode2KoDau(middleName);
		String _lastName = StringTool.unicode2KoDau(lastName);
		
		String tmpUsername = _firstName + _lastName.charAt(0);
		
		if (_middleName.length() > 0) {
			String[] spl = _middleName.split(" ");
			if (spl != null && spl.length >= 1) {
				for (int ind = 0; ind < spl.length; ind++) {
					tmpUsername += spl[ind].charAt(0);
				}
			}
			
		}
		tmpUsername = tmpUsername.toLowerCase();

		int i = 0;
		HreUser tmpUser = null;

		while (true) {
			
			String candidateName = null;
			if (i != 0) {
				candidateName = tmpUsername + i;
			} else {
				candidateName = tmpUsername + "";
			}

			tmpUser = baseService.getFirstPojo("userName", Compare.EQUAL, candidateName, null, null, 0, HreUser.class);
			
			if (tmpUser == null) {
				return candidateName;
			}
			
			i++;
		}
	}
	
	public String getCandidateUsername (String firstName, String middleName, String lastName) {

		if (firstName == null || middleName == null || lastName == null || firstName.length() <= 0
				|| lastName.length() <= 0) {
			return null;
		}
		
		String _firstName = StringTool.unicode2KoDau(firstName);
		String _middleName = StringTool.unicode2KoDau(middleName);
		String _lastName = StringTool.unicode2KoDau(lastName);
		
		String tmpUsername = _firstName + _lastName.charAt(0);
		
		if (_middleName.length() > 0) {
			String[] spl = _middleName.split(" ");
			if (spl != null && spl.length >= 1) {
				for (int ind = 0; ind < spl.length; ind++) {
					tmpUsername += spl[ind].charAt(0);
				}
			}
			
		}
		tmpUsername = tmpUsername.toLowerCase();

		return tmpUsername;
	}
}
